package bdsimple.impl.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bdsimple.abstractfactory.AbstractFactoryDAO;
import bdsimple.dao.base.ItemDAO;
import bdsimple.dao.base.ProductoDAO;
import bdsimple.dao.base.RecursoDAO;
import bdsimple.dto.base.ItemDTO;
import bdsimple.dto.base.PrecioItemCiudadDTO;
import bdsimple.dto.base.ProductoDTO;
import bdsimple.dto.base.RecursoDTO;
import bdsimple.dto.base.TarjetaMemoriaDTO;
import dao.base.api.IDTO;
import dao.base.impl.Reference;

public class ItemDAOImpl extends MySQLBaseDAO implements ItemDAO {

	public ItemDAOImpl() {
		super(ItemDTO.class);
	}

//-----------------------------------------------------------------------------	
	@Override
	protected String createInsertCollst(IDTO dto) throws Exception {

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(ItemDTOImpl.NOMBRE);
		strbuf.append(", ");
		strbuf.append(ItemDTOImpl.DESCRIPCION);
		strbuf.append(", ");
//		strbuf.append(ItemDTOImpl.IMAGEN);
//		strbuf.append(", ");
		strbuf.append(ItemDTO.TARJETA_MEMORIA_REFERENCIA);

		return strbuf.toString();

	}

//-------------------------------------------------------------------------------
	@Override
	protected String createInsertValues(IDTO dto) throws Exception {

		ItemDTOImpl itemDTOImpl = (ItemDTOImpl) dto;

	    StringBuffer strbuf = new StringBuffer();

	    strbuf.append(singleQuotes(itemDTOImpl.getNombre()));
	    
	    strbuf.append(", ");
	    
	    strbuf.append(singleQuotes(itemDTOImpl.getDescripcion()));
	    
	    strbuf.append(", ");
	    
	    Reference<TarjetaMemoriaDTO> ref = itemDTOImpl.getTarjetaMemoriaDTORef();
	    ref.checkInsert();
	    strbuf.append(ref.getIdAsString());

	    return strbuf.toString();

	}

//----------------------------------------------------------------------------------	
	@Override
	protected String createTableColumns() throws Exception {

		StringBuffer strbuf = new StringBuffer();
		
		ItemDAO itemDAO = (ItemDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO(//
				ItemDAO.class, connectionBean);

	    strbuf.append(ItemDTOImpl.ID);
	    strbuf.append(" INT PRIMARY KEY AUTO_INCREMENT, ");
	    strbuf.append(ItemDTOImpl.NOMBRE);
	    strbuf.append(" VARCHAR(50), ");
	    strbuf.append(ItemDTOImpl.DESCRIPCION);
	    strbuf.append(" VARCHAR(500), ");
//	    strbuf.append(ItemDTOImpl.IMAGEN);
//	    strbuf.append(" BLOB, ");
	    strbuf.append(ItemDTOImpl.TARJETA_MEMORIA_REFERENCIA);
		strbuf.append(" INT REFERENCES ");
		strbuf.append(itemDAO.getTableName());
	    
	    return strbuf.toString();

	}

	//------------------------------------------------------------------------------------	
	@Override
	protected String createUpdateValues(IDTO dto) throws Exception {

		ItemDTOImpl itemDTOImpl = (ItemDTOImpl) dto;

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(ItemDTOImpl.NOMBRE);
		strbuf.append(" = ");
		strbuf.append(singleQuotes(itemDTOImpl.getNombre()));
		
		strbuf.append(", ");
		
		strbuf.append(ItemDTOImpl.DESCRIPCION);
		strbuf.append(" = ");
		strbuf.append(singleQuotes(itemDTOImpl.getDescripcion()));
		
		strbuf.append(", ");
		
//		strbuf.append(ItemDTOImpl.IMAGEN);
//        strbuf.append(" = ");
//        strbuf.append();
		
		strbuf.append(ItemDTOImpl.TARJETA_MEMORIA_REFERENCIA);
		strbuf.append(" = ");
		
		Reference<TarjetaMemoriaDTO> ref = itemDTOImpl.getTarjetaMemoriaDTORef();
	    ref.checkUpdate();
	    strbuf.append(ref.getIdAsString());

		return strbuf.toString();

	}

	//---------------------------------------------------------------------------------------	
	@Override
	protected ItemDTOImpl internalResultSetToDTO(ResultSet rs, IDTO dto)
			throws Exception {

	    ItemDTOImpl ret = (ItemDTOImpl) dto;
	  
	    ret.setId(rs.getInt(ItemDTO.ID));
	    
	    ret.setNombre(rs.getString(ItemDTOImpl.NOMBRE));
	    
	    ret.setDescripcion(rs.getString(ItemDTO.DESCRIPCION));
	    
//	    ret.setImagen(rs.getBytes(ItemDTO.IMAGEN));
	    
	    Reference<TarjetaMemoriaDTO> ref = ret.getTarjetaMemoriaDTORef();
	    ref.setRefIdent(rs.getInt(ItemDTOImpl.TARJETA_MEMORIA_REFERENCIA));
	    ref.setRefValue(null);

	    return ret;

	}

	// -----------------------------------------------------------------------------------------

	protected ItemDTOImpl resultSetToDTO(ResultSet rs) throws Exception {

		ItemDTOImpl ret = //
			(ItemDTOImpl) dtaSession.getDtaByKey( //
					ItemDTOImpl.class, rs.getInt(ItemDTOImpl.ID));

		if (ret != null) {
			return ret;
		}

		if (rs.getString("dtoClass").equals(ProductoDTO.class.getName())) {
			ProductoDAO productoDAO = (ProductoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					ProductoDAO.class, connectionBean);

			ret = (ItemDTOImpl) productoDAO.loadById( //
					rs.getInt(ItemDTO.ID));

			return (ItemDTOImpl) dtaSession.add(ret);
		}

		if (rs.getString("dtoClass").equals(RecursoDTO.class.getName())) {
			RecursoDAO recursoDAO = (RecursoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					RecursoDAO.class, connectionBean);

			ret = (ItemDTOImpl) recursoDAO.loadById( //
					rs.getInt(ItemDTO.ID));

			return (ItemDTOImpl) dtaSession.add(ret);
		}

		throw new IllegalArgumentException(rs.getString("dtoClass"));
	}

	// ----------------------------------------------------------------
	// Insert
	// ----------------------------------------------------------------
	@Override
	public void insert(IDTO dto) throws Exception {
		if (dto instanceof ProductoDTO) {
			ProductoDAO productoDAO = (ProductoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					ProductoDAO.class, connectionBean);

			productoDAO.insert(dto);

			return;
		}

		if (dto instanceof RecursoDTO) {
			RecursoDAO recursoDAO = (RecursoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					RecursoDAO.class, connectionBean);

			recursoDAO.insert(dto);

			return;
		}
		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// ---------------------------------------------------------------
	// Update
	// ---------------------------------------------------------------
	@Override
	public void update(IDTO dto) throws Exception {
		
		if (dto instanceof ProductoDTO) {
			ProductoDAO productoDAO = (ProductoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					ProductoDAO.class, connectionBean);

			productoDAO.update(dto);

			return;
		}

		if (dto instanceof RecursoDTO) {
			RecursoDAO recursoDAO = (RecursoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					RecursoDAO.class, connectionBean);
			
			recursoDAO.update(dto);

			return;
		}

		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// --------------------------------------------------------------
	// Delete
	// --------------------------------------------------------------
	@Override
	public void delete(IDTO dto) throws Exception {
		if (dto instanceof ProductoDTO) {
			ProductoDAO productoDAO = (ProductoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					ProductoDAO.class, connectionBean);

			productoDAO.delete(dto);

			return;
		}

		if (dto instanceof RecursoDTO) {
			RecursoDAO recursoDAO = (RecursoDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					RecursoDAO.class, connectionBean);

			recursoDAO.delete(dto);

			return;
		}

		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// ----------------------------------------------------------------------
	@Override
	public List<IDTO> listAll(int lim, int off) throws Exception {

		ProductoDAO productoDAO = (ProductoDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				ProductoDAO.class, connectionBean);

		RecursoDAO recursoDAO = (RecursoDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				RecursoDAO.class, connectionBean);

		StringBuffer strbuf = new StringBuffer();

		// SELECT item.*, 'bdsimple.base.ProductoDTO' AS dtoClass FROM
		// item, producto
		// WHERE
		// producto.id == item.id
		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				ProductoDTO.class.getName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + productoDAO.getTableName());
		strbuf.append(" WHERE  " + //
				productoDAO.getTableName() + "." + ProductoDTO.ID + //
				" = " + //
				this.getTableName() + "." + ItemDTO.ID);

		strbuf.append(" UNION ");

		// SELECT item.*, 'bdsimple.base.RecursoDTO' AS dtoClass FROM
		// item, recurso
		// WHERE
		// recurso.id == item.id
		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				RecursoDTO.class.getName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + recursoDAO.getTableName());
		strbuf.append(" WHERE  " + //
				recursoDAO.getTableName() + "." + RecursoDTO.ID + //
				" = " + //
				this.getTableName() + "." + ItemDTO.ID);

		if (lim >= 0 && off >= 0) {
			strbuf.append(" LIMIT  ");
			strbuf.append(lim);
			strbuf.append(" OFFSET ");
			strbuf.append(off);
		}

		System.err.println(strbuf.toString());

		ResultSet rs = //
			connection.createStatement().executeQuery(strbuf.toString());

		List<IDTO> ret = new ArrayList<IDTO>();

		while (rs.next()) {
			ret.add(resultSetToDTO(rs));
		}

		return ret;
	}

	// ----------------------------------------------------------------------------
	@Override
	public List<IDTO> listBy(String key, Object val) throws Exception {
		ProductoDAO productoDAO = (ProductoDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				ProductoDAO.class, connectionBean);

		RecursoDAO recursoDAO = (RecursoDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				RecursoDAO.class, connectionBean);

		StringBuffer strbuf = new StringBuffer();

		// SELECT item.*, 'bdsimple.base.ProductoDTO' AS dtoClass FROM
		// item, producto
		// WHERE
		// producto.id == item.id
		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				ProductoDTO.class.getName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + productoDAO.getTableName());
		strbuf.append(" WHERE  " + //
				productoDAO.getTableName() + "." + ProductoDTO.ID + //
				" = " + //
				this.getTableName() + "." + ItemDTO.ID);
		strbuf.append(" AND ");
		strbuf.append(key);
		strbuf.append(" = ");
		strbuf.append(val);

		strbuf.append(" UNION ");

		// SELECT item.*, 'bdsimple.base.RecursoDTO' AS dtoClass FROM
		// item, recurso
		// WHERE
		// recurso.id == item.id
		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				RecursoDTO.class.getName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + recursoDAO.getTableName());
		strbuf.append(" WHERE  " + //
				recursoDAO.getTableName() + "." + RecursoDTO.ID + //
				" = " + //
				this.getTableName() + "." + ItemDTO.ID);
		strbuf.append(" AND ");
		strbuf.append(key);
		strbuf.append(" = ");
		strbuf.append(val);

		System.err.println(strbuf.toString());

		ResultSet rs = //
			connection.createStatement().executeQuery(strbuf.toString());

		List<IDTO> ret = new ArrayList<IDTO>();

		while (rs.next()) {
			ret.add(resultSetToDTO(rs));
		}

		return ret;
	}
	
	public byte[] loadImageByItemId(int articuloId) throws SQLException {
        StringBuffer strbuf = new StringBuffer();

        strbuf.append("SELECT ");
        strbuf.append(ItemDTO.IMAGEN);
        strbuf.append(" FROM ");
        strbuf.append(getTableName());
        strbuf.append(" WHERE ");
        strbuf.append(ItemDTO.ID);
        strbuf.append(" = ");
        strbuf.append(articuloId);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        if (rs.next()) {
            return rs.getBytes(ItemDTO.IMAGEN);
        }

        return null;
    }
	
	public List<ItemDTO> listByCiudadId(int id) throws Exception { 
        StringBuffer strbuf = new StringBuffer();

        PrecioItemCiudadDAOImpl ciudadArticuloDAO = new PrecioItemCiudadDAOImpl();
        ItemDAO articuloDAOImpl = new ItemDAOImpl();

        strbuf.append("SELECT " + getTableName() + ".* FROM ");
        strbuf.append(getTableName());
        strbuf.append(" JOIN " + ciudadArticuloDAO.getTableName());
        strbuf.append(" ON " + articuloDAOImpl.getTableName() + "." + ItemDTO.ID + " = " + PrecioItemCiudadDTO.ITEM_REFERENCIA);
        strbuf.append(" WHERE " + PrecioItemCiudadDTO.CIUDAD_REFERENCIA + " = " + id);

        System.err.println(strbuf.toString());

        ResultSet rs = //
        connection.createStatement().executeQuery(strbuf.toString());

        List<ItemDTO> ret = new ArrayList<ItemDTO>();

        while (rs.next()) {
            ret.add(resultSetToDTO(rs));
        }

        return ret;
    }
	
}
